﻿Imports System.Data.SqlClient
Public Class Usuarios

    Private Const ConectionString As String = "Data Source=TOSHIBA-PC\SQLSERVER;Initial Catalog=Banco;Integrated Security=True"

    Sub Agregar(ByVal usuarioamv As Banco.BE.Usuarios)
        Dim sqlConnection As SqlConnection = New SqlConnection()
        Dim sqlCommand As SqlCommand = New SqlCommand()

        Try
            sqlConnection.ConnectionString = ConectionString


            sqlCommand.Parameters.Add(New SqlParameter("NombreCompleto", usuarioamv.NombreCompleto))
            sqlCommand.Parameters.Add(New SqlParameter("Identificacion", usuarioamv.Identificacion))
            sqlCommand.Parameters.Add(New SqlParameter("NombreUsuario", usuarioamv.NombreUsuario))
            sqlCommand.Parameters.Add(New SqlParameter("Contraseña", usuarioamv.contrasena))
            sqlCommand.Parameters.Add(New SqlParameter("Perfil", usuarioamv.Perfil))
            sqlCommand.Parameters.Add(New SqlParameter("Estado", usuarioamv.Estado))
            sqlCommand.Parameters.Add(New SqlParameter("FechaCreacion", usuarioamv.FechaCreacion))


            sqlConnection.Open()

            sqlCommand.CommandText = "UsuarioAgregar"
            sqlCommand.CommandType = CommandType.StoredProcedure
            sqlCommand.Connection = sqlConnection
            sqlCommand.ExecuteNonQuery()
            sqlConnection.Close()

        Catch Ex As Exception
            Throw New Exception("Problemas al Insertar" & Ex.ToString)
        Finally
            If sqlConnection.State <> ConnectionState.Closed Then
                sqlConnection.Close()
            End If
        End Try

    End Sub

    Function Listar() As List(Of Banco.BE.Usuarios)

        Dim lista As New List(Of Banco.BE.Usuarios)
        Dim conexion As New SqlClient.SqlConnection
        conexion.ConnectionString = ConectionString
        Dim comando As New SqlClient.SqlCommand

        comando.Connection = conexion
        comando.CommandType = CommandType.StoredProcedure
        comando.CommandText = "UsuariosListar"

        Dim lector As SqlClient.SqlDataReader
        Try
            conexion.Open()
            lector = comando.ExecuteReader()

            If lector.HasRows Then
                While lector.Read
                    Dim usuario As New BE.Usuarios
                    usuario.Id = CInt(lector.GetValue(0))
                    usuario.NombreCompleto = lector.GetValue(1).ToString
                    usuario.Identificacion = lector.GetValue(2).ToString

                    usuario.NombreUsuario = lector.GetValue(3).ToString
                    usuario.contrasena = lector.GetValue(4).ToString
                    usuario.Perfil = CInt(lector.GetValue(5))
                    usuario.Estado = CInt(lector.GetValue(6))
                    usuario.FechaCreacion = CDate(lector.GetValue(7))
                    lista.Add(usuario)

                End While
            End If

            lector.Close()

        Catch ex As Exception

            Throw New Exception("Problemas al listar")

        Finally
            conexion.Close()

        End Try

        Return lista

    End Function

    Function Editar(ByVal mantusuario As Banco.BE.Usuarios) As Boolean
        Dim sqlConnection As SqlConnection = New SqlConnection()
        Dim sqlCommand As SqlCommand = New SqlCommand()

        Try
            sqlConnection.ConnectionString = ConectionString


            sqlCommand.Parameters.Add(New SqlParameter("NombreCompleto", mantusuario.NombreCompleto))
            sqlCommand.Parameters.Add(New SqlParameter("Identificacion", mantusuario.Identificacion))
            sqlCommand.Parameters.Add(New SqlParameter("NombreUsuario", mantusuario.NombreUsuario))
            sqlCommand.Parameters.Add(New SqlParameter("Contraseña", mantusuario.contrasena))
            sqlCommand.Parameters.Add(New SqlParameter("Perfil", mantusuario.Perfil))
            sqlCommand.Parameters.Add(New SqlParameter("Estado", mantusuario.Estado))
            sqlCommand.Parameters.Add(New SqlParameter("FechaCreacion", mantusuario.FechaCreacion))
            sqlCommand.Parameters.Add(New SqlParameter("Id", mantusuario.Id))

            sqlConnection.Open()

            sqlCommand.CommandText = "UsuarioEditar"
            sqlCommand.CommandType = CommandType.StoredProcedure
            sqlCommand.Connection = sqlConnection
            sqlCommand.ExecuteNonQuery()
            sqlConnection.Close()

        Catch Ex As Exception
            Throw New Exception("Problemas al Editar Registro" & Ex.ToString)
            Return False
        Finally
            If sqlConnection.State <> ConnectionState.Closed Then
                sqlConnection.Close()
            End If
        End Try

        Return True
    End Function

    Function Obtener(ByVal id As Integer) As Banco.BE.Usuarios
        Dim sqlConnection As SqlConnection = New SqlConnection()
        Dim sqlCommand As SqlCommand = New SqlCommand()
        Dim myReader As SqlDataReader
        Dim resultado As Banco.BE.Usuarios = Nothing

        Try
            sqlConnection.ConnectionString = ConectionString

            sqlConnection.Open()

            sqlCommand.CommandText = "UsuarioObtener"
            sqlCommand.CommandType = CommandType.StoredProcedure
            sqlCommand.Connection = sqlConnection

            sqlCommand.Parameters.Add(New SqlParameter("Id", id)) ' de donde sale este id

            myReader = sqlCommand.ExecuteReader()

            If myReader.HasRows Then
                Do While myReader.Read()
                    resultado = New Banco.BE.Usuarios
                    resultado.Id = CInt(myReader.GetValue(0))
                    resultado.NombreCompleto = myReader.GetValue(1).ToString
                    resultado.Identificacion = myReader.GetValue(2).ToString
                    resultado.NombreUsuario = myReader.GetValue(3).ToString
                    resultado.contrasena = myReader.GetValue(4).ToString
                    resultado.Perfil = CInt(myReader.GetValue(5))
                    resultado.Estado = CInt(myReader.GetValue(6))
                    resultado.FechaCreacion = CDate(myReader.GetValue(7))
                Loop

            End If

            myReader.Close()

        Catch Ex As Exception
            Throw New Exception("Problemas al Obtener" & Ex.ToString)
        Finally

            If sqlConnection.State <> ConnectionState.Closed Then
                sqlConnection.Close()
            End If
        End Try


        Return resultado
    End Function

    Function Devolver(ByVal id As Integer) As Boolean
        Dim sqlConnection As SqlConnection = New SqlConnection()
        Dim sqlCommand As SqlCommand = New SqlCommand()

        Try
            sqlConnection.ConnectionString = ConectionString
            sqlCommand.Parameters.Add(New SqlParameter("Id", id))

            sqlConnection.Open()

            sqlCommand.CommandText = "UsuarioDevolver"
            sqlCommand.CommandType = CommandType.StoredProcedure
            sqlCommand.Connection = sqlConnection
            sqlCommand.ExecuteNonQuery()
            sqlConnection.Close()

        Catch Ex As Exception
            Throw New Exception("Problemas al Editar en la Bd" & Ex.ToString)
            Return False
        Finally
            If sqlConnection.State <> ConnectionState.Closed Then
                sqlConnection.Close()
            End If
        End Try

        Return True
    End Function

    Function Devolver2(ByVal id As Integer) As Boolean
        Dim sqlConnection As SqlConnection = New SqlConnection()
        Dim sqlCommand As SqlCommand = New SqlCommand()

        Try
            sqlConnection.ConnectionString = ConectionString
            sqlCommand.Parameters.Add(New SqlParameter("Id", id))

            sqlConnection.Open()

            sqlCommand.CommandText = "UsuarioDevolver2"
            sqlCommand.CommandType = CommandType.StoredProcedure
            sqlCommand.Connection = sqlConnection
            sqlCommand.ExecuteNonQuery()
            sqlConnection.Close()

        Catch Ex As Exception
            Throw New Exception("Problemas al Editar en la Bd" & Ex.ToString)
            Return False
        Finally
            If sqlConnection.State <> ConnectionState.Closed Then
                sqlConnection.Close()
            End If
        End Try

        Return True
    End Function

    Function ListarHabilitado() As List(Of Banco.BE.Usuarios)

        Dim lista As New List(Of Banco.BE.Usuarios)
        Dim conexion As New SqlClient.SqlConnection
        conexion.ConnectionString = ConectionString
        Dim comando As New SqlClient.SqlCommand

        comando.Connection = conexion
        comando.CommandType = CommandType.StoredProcedure
        comando.CommandText = "UsuarioListarHabilitado"

        Dim lector As SqlClient.SqlDataReader
        Try
            conexion.Open()
            lector = comando.ExecuteReader()

            If lector.HasRows Then
                While lector.Read
                    Dim usuario As New BE.Usuarios
                    usuario.Id = CInt(lector.GetValue(0))
                    usuario.NombreCompleto = lector.GetValue(1).ToString
                    usuario.Identificacion = lector.GetValue(2).ToString

                    usuario.NombreUsuario = lector.GetValue(3).ToString
                    usuario.contrasena = lector.GetValue(4).ToString
                    usuario.Perfil = CInt(lector.GetValue(5))
                    usuario.Estado = CInt(lector.GetValue(6))
                    usuario.FechaCreacion = CDate(lector.GetValue(7))
                    lista.Add(usuario)

                End While
            End If

            lector.Close()

        Catch ex As Exception

            Throw New Exception("Problemas al Filtrar")

        Finally
            conexion.Close()

        End Try

        Return lista

    End Function

    Function ListarInhabilitado() As List(Of Banco.BE.Usuarios)

        Dim lista As New List(Of Banco.BE.Usuarios)
        Dim conexion As New SqlClient.SqlConnection
        conexion.ConnectionString = ConectionString
        Dim comando As New SqlClient.SqlCommand

        comando.Connection = conexion
        comando.CommandType = CommandType.StoredProcedure
        comando.CommandText = "UsuariosListarInhabilitado"

        Dim lector As SqlClient.SqlDataReader
        Try
            conexion.Open()
            lector = comando.ExecuteReader()

            If lector.HasRows Then
                While lector.Read
                    Dim usuario As New BE.Usuarios
                    usuario.Id = CInt(lector.GetValue(0))
                    usuario.NombreCompleto = lector.GetValue(1).ToString
                    usuario.Identificacion = lector.GetValue(2).ToString

                    usuario.NombreUsuario = lector.GetValue(3).ToString
                    usuario.contrasena = lector.GetValue(4).ToString
                    usuario.Perfil = CInt(lector.GetValue(5))
                    usuario.Estado = CInt(lector.GetValue(6))
                    usuario.FechaCreacion = CDate(lector.GetValue(7))
                    lista.Add(usuario)

                End While
            End If

            lector.Close()

        Catch ex As Exception

            Throw New Exception("Problemas al Filtrar")

        Finally
            conexion.Close()

        End Try

        Return lista

    End Function
End Class
